package com.cosin.util;

import com.cosin.model.dto.NodeDTO;
import com.cosin.model.po.NodePO;
import com.cosin.model.po.RelationPO;
import com.cosin.service.GraphService;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import static java.sql.Types.BOOLEAN;
import static java.sql.Types.NUMERIC;
import static org.apache.poi.ss.usermodel.DataValidationConstraint.ValidationType.FORMULA;
import static org.apache.xmlbeans.impl.piccolo.xml.Piccolo.STRING;


@Slf4j
@Service
public class ReadExcel {

    private static final DataFormatter dataFormatter = new DataFormatter();

    private static GraphService graphService;

    @Autowired
    ReadExcel (GraphService graphService){
        ReadExcel.graphService = graphService;
    }



    /**
     * 读取文件
     * @param
     */
    public static List<NodePO> readExcelFile(InputStream inputStream,int projectId) throws IOException {
        List<NodePO> nodePOList = new ArrayList<>();
        try (Workbook workbook = new XSSFWorkbook(inputStream)) {
            // 处理企业工作表
            Sheet enterpriseSheet = workbook.getSheet("企业");
            if (enterpriseSheet != null) {
                nodePOList.addAll(processSheet(enterpriseSheet, "企业",projectId));
            }

            // 处理专利工作表
            Sheet patentSheet = workbook.getSheet("专利");
            if (patentSheet != null) {
                nodePOList.addAll(processSheet(patentSheet, "专利",projectId));
            }

            // 处理产品工作表
            Sheet productSheet = workbook.getSheet("产品");
            if (productSheet != null) {
                nodePOList.addAll(processSheet(productSheet, "产品",projectId));
            }
        }
        return nodePOList;
    }

    // 处理每个工作表的数据
    // 将数据转换成节点信息
    private static List<NodePO> processSheet(Sheet sheet, String group,int projectId) {
        List<NodePO> nodePOList = new ArrayList<>();
        Row headerRow = sheet.getRow(0);
        int columnCount = headerRow.getLastCellNum();

        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            NodePO nodePO = new NodePO();
            // TODO 测试时将类别号 + “1”
            nodePO.setGroup(group + "1");
            nodePO.setRadius(1);
            nodePO.setProjectId(projectId);
            nodePO.setColor("");
            nodePO.setTextSize("15");
            nodePO.setFigure("");

            Map<String, String> properties = new HashMap<>();
            for (int j = 0; j < columnCount; j++) {
                Cell cell = row.getCell(j);
                String headerValue = getCellValueAsString(headerRow.getCell(j));
                String cellValue = getCellValueAsString(cell);
                // 如果是名称则设置到节点中, 其他属性则设置到节点属性中
                // 如果属性名称或者属性值为空 则不填入
                if ("企业名称".equals(headerValue) || "专利名称".equals(headerValue) || "产品名称".equals(headerValue)) {
                    nodePO.setName(cellValue);
                } else if (!headerValue.isEmpty() && !cellValue.isEmpty()) {
                    properties.put(headerValue, cellValue);
                }
            }
            ObjectMapper objectMapper = new ObjectMapper();
            try {
                nodePO.setPropertiesAsString(objectMapper.writeValueAsString(properties));
            } catch (Exception e) {
                log.info("JSON 转换失败: " + e.getMessage());
            }
            // 如果 必要的 名称 不为空 进入列表
            if(!nodePO.getName().isEmpty()){
                nodePOList.add(nodePO);
            }
        }
        return nodePOList;
    }

    // 处理产品工作表 从产品工作表中读取出节点关系信息
    public static List<RelationPO> extractRelationsFromExcel(InputStream inputStream, int projectId) throws IOException {
        List<RelationPO> relationPOList = new ArrayList<>();
        try (Workbook workbook = new XSSFWorkbook(inputStream)) {
            Sheet productSheet = workbook.getSheet("产品");
            if (productSheet != null) {
                relationPOList.addAll(processProductSheet(productSheet, projectId));
            }
        }
        return relationPOList;
    }
    private static List<RelationPO> processProductSheet(Sheet sheet, int projectId) {
        List<RelationPO> relationPOList = new ArrayList<>();
        Row headerRow = sheet.getRow(0);
        int columnCount = headerRow.getLastCellNum(); // 获取总列数
        int rowCount = sheet.getLastRowNum(); // 获取总行数
        int nameColumnIndex = -1; // 产品名称下标
        int modelColumnIndex = -1; // 产品型号下标
        List<Integer> companyNameIndex = new ArrayList<>();// 公司名称下标
        List<Integer> patentNumIndex = new ArrayList<>();// 专利号下标
        // 根据标头获取相应列的下标
        for (int i = 0; i <= columnCount; i++) {
            if("产品名称".equals(getCellValueAsString(headerRow.getCell(i)))){
                nameColumnIndex = i;
            } else if("产品型号".equals(getCellValueAsString(headerRow.getCell(i)))){
                modelColumnIndex = i;
            } else if ("所属单位".equals(getCellValueAsString(headerRow.getCell(i)))) {
                companyNameIndex.add(i);
            } else if ("专利号".equals(getCellValueAsString(headerRow.getCell(i)))) {
                patentNumIndex.add(i);
            }
        }
        // 创建 产品 与 公司 的生产关系
        for(int i = 1; i <= rowCount; i++){
            Row row = sheet.getRow(i);
            // 行中的产品型号不为空
            String productModel = getCellValueAsString(row.getCell(modelColumnIndex));
            if(!"".equals(productModel)){
                // 遍历行中的公司名称，如果不为空，则创建关系
                for(int j : companyNameIndex){
                    String companyName = getCellValueAsString(row.getCell(j));
                    // 如果公司名称不为空，则获取公司节点的nodeId，并确定为source
                    if(!"".equals(companyName)){
                        // 得到公司相应的nodeId
                        long source = graphService.getNodeIdByCompanyNameAndProjectId(companyName,projectId);
                        // 得到产品相应的nodeId
                        long target = graphService.getNodeIdByProductModelAndProjectId(productModel, projectId);
                        // 填充节点信息
                        RelationPO relationPO = new RelationPO();
                        relationPO.setSource(source);
                        relationPO.setTarget(target);
                        relationPO.setName("生产");
                        relationPO.setWidth(1);
                        relationPO.setProjectId(projectId);
                        relationPOList.add(relationPO);
                    }
                }
                // 创建 产品 与 专利 的关联关系
                for (int j : patentNumIndex) {
                    String patentNum = getCellValueAsString(row.getCell(j));
                    if (!"".equals(patentNum)) {
                        // 得到产品的nodeId
                        long source = graphService.getNodeIdByProductModelAndProjectId(productModel, projectId);
                        // 得到专利的nodeId
                        long target = graphService.getNodeIdByPatentModelAndProjectId(patentNum, projectId);
                        // 填充节点信息
                        RelationPO relationPO = new RelationPO();
                        relationPO.setSource(source);
                        relationPO.setTarget(target);
                        relationPO.setName("引用");
                        relationPO.setWidth(1);
                        relationPO.setProjectId(projectId);
                        relationPOList.add(relationPO);
                    }
                }
            }
        }
        return relationPOList;
    }
    // 读取单元格值并转换为字符串
    private static String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return "";
        }
        // 去除字符串前后的空格
        return dataFormatter.formatCellValue(cell).trim();
    }
}
